/*
 * Created on Aug 2, 2004
 */
package com.scalar.chiptrack.operations.process.dao.gp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.process.dao.WorkflowDBManager;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;

/**
 * @author Ravikanth
**/
public class GPWorkFlowDAO
{
    /**
     * @param dbTransaction
     * @param activityCollection
     */
    public static void saveProducts(DBTransaction dbTransaction, ArrayList activityCollection)
    {
		boolean success 	 = false;
		HashMap activity	 = null; 
		
    	try
    	{
    		if( activityCollection != null && activityCollection.isEmpty() == false )
    		{
				for( int i = 0; i < activityCollection.size(); i++ )
				{
					activity = (HashMap)activityCollection.get( i );
					saveActivity( dbTransaction, activity );	
				}
    		}
    	}
    	catch( Exception e )
    	{
    		e.printStackTrace();
    	}
    }

    /**
     * @param dbTransaction
     * @param activity
     */
    private static void saveActivity( DBTransaction dbTransaction, HashMap activityMap )
    {
		Connection conn 	 = null;
		PreparedStatement pstmt = null;
		ResultSet rs 		 = null;
		boolean success = false;
		
		String processType 	= null;
		String processName 	= null;
		String partNumber 	= null;
		String vendors		= null;
		String vendorName	= null;
		String vendorArr	= "";
		String description	= null;
		String gpPartNumber = null;
		boolean productPresent = false;

		if( activityMap.containsKey( "Type" ) )
			processType = (String)activityMap.get( "Type" );

		if( activityMap.containsKey( "value" ) )
			processName = (String)activityMap.get( "value" );

		if( activityMap.containsKey( "partnumber" ) )
			partNumber = (String)activityMap.get( "partnumber" );

		if( activityMap.containsKey( "vendors" ) )
			vendors = (String)activityMap.get( "vendors" );
			
		if( activityMap.containsKey( "comments" ) )
			description = (String)activityMap.get( "comments" );	
		
		try
		{
			if( partNumber != null && processName != null )
			{
				ArrayList tempList = WorkflowDBManager.parseString( vendors, '#' );
				ArrayList vendorDetail = null;
					
				if( tempList != null )
				{
					Iterator it = tempList.iterator();
					
					while( it.hasNext() )
					{
						vendorDetail = WorkflowDBManager.parseString( (String)it.next(), ';' );
							
						if( vendorDetail != null && vendorDetail.size() >= 3 )
						{
							vendorName = (String)vendorDetail.get( 2 );
							if( vendorName.indexOf( '/' ) != -1 )
							{
								vendorName = vendorName.substring( 0, vendorName.indexOf( '/' ) );
								vendorArr = vendorArr + vendorName + "$";   
							}
						}
					}
				}
				
				vendorArr = VendorDAO.getGPVendorIdsAsString( vendorArr );

				if( dbTransaction != null )
				{
					conn = dbTransaction.getConnection( Constants.DATASOURCE_TYPE_GREAT_PLAINS );
				}
				else
				{
					conn = DataSource.getConnection( Constants.DATASOURCE_TYPE_GREAT_PLAINS );
				}
				
				pstmt = conn.prepareCall( "{ call sp_ct_prod_save_products( ?, ?, ?, ?, ?, ? ) }" );
				pstmt.setString( 1, partNumber );
				pstmt.setString( 2, processName );
				pstmt.setString( 3, processType );
				pstmt.setString( 4, vendorArr );
				pstmt.setString( 5, description );
				pstmt.setString( 6, "CHIP" );
				pstmt.execute();
				
				//System.out.println( "Executed the proc" );
				/*pstmt = conn.prepareStatement( "SELECT ITEMNMBR FROM IV00101 WHERE ITEMNMBR = ?" );
				pstmt.setString( 1, gpPartNumber );
				
				rs = pstmt.executeQuery(); 
				
				if( rs.next() )
				{
					productPresent = true;			
				}

				if( rs != null ){	try{ rs.close(); }catch( SQLException ignore ){}	}
				if( pstmt != null ){ try{ pstmt.close(); }catch( SQLException ignore ){}	}
				
				if( !productPresent )
				{
					// Step#1
					// Insert into Item Table
					pstmt = conn.prepareStatement( "INSERT INTO IV00101 (ITEMNMBR, ITEMDESC, ITEMTYPE, CREATDDT, ITMSHNAM, ITMGEDSC, VCTNMTHD, TAXOPTNS, ITMTSHID, UOMSCHDL, ITMTRKOP, Purchase_Tax_Options, STNDCOST, CURRCOST, DECPLQTY, DECPLCUR, Purchase_Item_Tax_Schedu) values( SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 31 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 51 ), 1, ?, SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 15 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 11 ), 1, 1, 'ALL DETAILS', 'EACH', 1, 2, 0.0000, 0.0000, 1, 3, '')" );
					
					pstmt.setString( 1, gpPartNumber );
					pstmt.setString( 2, description );
					pstmt.setDate( 3, StringUtils.getSQLDate( new Date() ) );
					pstmt.setString( 4, gpPartNumber );
					pstmt.setString( 5, gpPartNumber );
					
					pstmt1 = conn.prepareStatement( "INSERT INTO IV00102 ( ITEMNMBR, RCRDTYPE ) values ( SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 31 ), 1 )" );
					pstmt1.setString( 1, gpPartNumber );
					
					pstmt.executeUpdate();
					pstmt1.executeUpdate(); 
					
					if( pstmt != null ){ try{ pstmt.close(); }catch( SQLException ignore ){}	}
					if( pstmt1 != null ){ try{ pstmt1.close(); }catch( SQLException ignore ){}	}
					
					// Step#2
					// Insert into Vendor Item Table
					// Insert into IV00103 ( ITEMNMBR, VENDORID, VNDITNUM, VNDITDSC ) values ( 'Part15', 'CT_VENDOR', 'Part15', 'Part15' )
					
					ArrayList tempList = WorkflowDBManager.parseString( vendors, '#' );
					ArrayList vendorDetail = null;
					
					if( tempList != null )
					{
						Iterator it = tempList.iterator();
					
						while( it.hasNext() )
						{
							vendorDetail = WorkflowDBManager.parseString( (String)it.next(), ';' );
							
							if( vendorDetail != null && vendorDetail.size() >= 3 )
							{
								vendorName = (String)vendorDetail.get( 2 );
								if( vendorName.indexOf( '/' ) != -1 )
								{
									vendorName = vendorName.substring( 0, vendorName.indexOf( '/' ) );
								}
								System.out.println( "Vendor Name "+ vendorName );
								pstmt = conn.prepareStatement( "Insert into IV00103 ( ITEMNMBR, VENDORID, VNDITNUM, VNDITDSC ) values ( SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 31 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 15 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 31 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 51 ) )" );
								pstmt.setString( 1, gpPartNumber );
								pstmt.setString( 2, vendorName );
								pstmt.setString( 3, gpPartNumber );
								pstmt.setString( 4, gpPartNumber );
								
								pstmt.addBatch();
								
								pstmt1 = conn.prepareStatement( "Insert into IV00102 (ITEMNMBR, LOCNCODE, RCRDTYPE) values ( SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 31 ), SUBSTRING( LTRIM( RTRIM ( ? ) ), 1, 11 ), 2 )" );
								pstmt1.setString( 1, gpPartNumber );
								pstmt1.setString( 2, vendorName + "/" + processType );
								
								pstmt1.addBatch();
							}
						}
						pstmt.executeBatch();
						pstmt1.executeBatch();
					}
				}*/
			}
			success = true;
		}
		catch( Exception e)
		{
			e.printStackTrace();	
		}
		finally
		{
			if( dbTransaction == null )
			{
				if( success )
					try {  conn.commit();  }catch (SQLException e1) {  e1.printStackTrace();  }
				else
					try {  conn.rollback();  }catch (SQLException e1) {  e1.printStackTrace();  }
			}
			if(rs != null){
				try{ rs.close(); }catch(SQLException ignore){}
			}
			if(pstmt != null){
				try{ pstmt.close(); }catch(SQLException ignore){}
			}
			//if(pstmt1 != null){
			//	try{ pstmt1.close(); }catch(SQLException ignore){}
			//}
			if( conn != null){
				try{conn.close(); }catch(SQLException ignore){}
			}
		}
    }
}